package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import objects.DistanceLog;
import objects.GeneralLog;
import objects.Instructor;
import objects.NewSpeedLimit;
import objects.NewStopSign;
import objects.School;
import objects.SpeedLimit;
import objects.SpeedLog;
import objects.SpeedLogReport;
import objects.StopSign;
import objects.StopSignLog;
import objects.Student;

public class CSdb {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public CSdb() throws Exception {
	
		try {
			// This will load the MySQL driver, each DB has its own driver
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException X) {
				System.out.println("Driver could not be loadded!!!");
			}
			// Setup the connection with the DB
			String pw = "c$411r3d$p10";
			connect = DriverManager.getConnection("jdbc:mysql://raphael.cs.odu.edu/411redsp10?" + "user=411redsp10&password="+pw);

			
		} catch (Exception e) {
			throw e;
		}
	}

	public void getSchool( School school1) throws SQLException {

        // Statements allow to issue SQL queries to the database
		String stmt1 = "SELECT * FROM 411redsp10.School WHERE school_id = ?";
        preparedStatement = connect.prepareStatement(stmt1);
		preparedStatement.setInt(1,school1.getID());
        resultSet = preparedStatement.executeQuery();

		while (resultSet.next()) {
			school1.setAddress(resultSet.getString("address"));
			school1.setName(resultSet.getString("name"));
			school1.setOwner(resultSet.getString("owner"));
		}
	}

    public void getStudent( Student student1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt2 = "SELECT * FROM 411redsp10.Student WHERE student_id = ?";
        preparedStatement = connect.prepareStatement(stmt2);
        preparedStatement.setInt(1,student1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            student1.setName(resultSet.getString("name"));
            student1.setInstrID(resultSet.getInt("instr_id"));
            student1.setSchoolID(resultSet.getInt("school_id"));
            student1.setHours(resultSet.getDouble("hours"));
            student1.setAddress(resultSet.getString("address"));
            student1.setFingerPrint(resultSet.getBlob("fingerprint"));
        }
    }

    public void getInstructor( Instructor instructor1) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt4 = "SELECT * FROM 411redsp10.Instructor WHERE instr_id = ?";
        preparedStatement = connect.prepareStatement(stmt4);
        preparedStatement.setInt(1,instructor1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            instructor1.setName(resultSet.getString("name"));
            instructor1.setSchoolID(resultSet.getInt("school_id"));
        }
    }



    public void getSpeedLimit( SpeedLimit speedlimit1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt5 = "SELECT * FROM 411redsp10.SpeedLimits WHERE location_id = ?";
        preparedStatement = connect.prepareStatement(stmt5);
        preparedStatement.setInt(1,speedlimit1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            speedlimit1.setP1Lat(resultSet.getDouble("point1Lat"));
            speedlimit1.setP1Long(resultSet.getDouble("point1Long"));
            speedlimit1.setP2Lat(resultSet.getDouble("point2Lat"));
            speedlimit1.setP2Long(resultSet.getDouble("point2Long"));
            speedlimit1.setLength(resultSet.getDouble("length"));
            speedlimit1.setWidth(resultSet.getDouble("width"));
            speedlimit1.setMaxSpeed(resultSet.getInt("maxspeed"));
        }
    }

    public void getStopSign( StopSign sts1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt7 = "SELECT * FROM 411redsp10.StopSigns WHERE location_id = ?";
        preparedStatement = connect.prepareStatement(stmt7);
        preparedStatement.setInt(1,sts1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            sts1.setP1Lat(resultSet.getDouble("point1Lat"));
            sts1.setP1Long(resultSet.getDouble("point1Long"));
            sts1.setP2Lat(resultSet.getDouble("point2Lat"));
            sts1.setP2Long(resultSet.getDouble("point2Long"));
            sts1.setLength(resultSet.getDouble("length"));
            sts1.setWidth(resultSet.getDouble("width"));
            sts1.setTriggerCheck(resultSet.getBoolean("triggerCheck"));
        }
    }


    public void getNewSpeedLimit( NewSpeedLimit speedlimit1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt6 = "SELECT * FROM 411redsp10.SpeedLimitsNew WHERE location_id = ?";
        preparedStatement = connect.prepareStatement(stmt6);
        preparedStatement.setInt(1,speedlimit1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            speedlimit1.setP1Lat(resultSet.getDouble("point1Lat"));
            speedlimit1.setP1Long(resultSet.getDouble("point1Long"));
            speedlimit1.setP2Lat(resultSet.getDouble("point2Lat"));
            speedlimit1.setP2Long(resultSet.getDouble("point2Long"));
            speedlimit1.setLength(resultSet.getDouble("length"));
            speedlimit1.setWidth(resultSet.getDouble("width"));
            speedlimit1.setMaxSpeed(resultSet.getInt("maxspeed"));
        }
    }

    public void getNewStopSign( NewStopSign sts1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt8 = "SELECT * FROM 411redsp10.StopSignsNew WHERE location_id = ?";
        preparedStatement = connect.prepareStatement(stmt8);
        preparedStatement.setInt(1,sts1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            sts1.setP1Lat(resultSet.getDouble("point1Lat"));
            sts1.setP1Long(resultSet.getDouble("point1Long"));
            sts1.setP2Lat(resultSet.getDouble("point2Lat"));
            sts1.setP2Long(resultSet.getDouble("point2Long"));
            sts1.setLength(resultSet.getDouble("length"));
            sts1.setWidth(resultSet.getDouble("width"));
            sts1.setTriggerCheck(resultSet.getBoolean("triggerCheck"));
        }
    }


    public void getStopSignLog( StopSignLog stsl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt9 = "SELECT * FROM 411redsp10.StopLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt9);
        preparedStatement.setInt(1,stsl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            stsl1.setType(resultSet.getInt("type"));
            stsl1.setStudentID(resultSet.getInt("student_id"));
			stsl1.setLocationID(resultSet.getInt("location_id"));
			stsl1.setTimeSt(resultSet.getTimestamp("timestamp"));
        }
    }

    public void getGeneralLog( GeneralLog genl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt10 = "SELECT * FROM 411redsp10.GenLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt10);
        preparedStatement.setInt(1,genl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            //id = resultSet.getInt("instr_id");
            genl1.setType(resultSet.getInt("type"));
            genl1.setStudentID(resultSet.getInt("student_id"));
            genl1.setLocationID(resultSet.getInt("location_id"));
            genl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            genl1.setDuration(resultSet.getInt("duration"));
        }
    }

    public void getSpeedLog( SpeedLog spl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt11 = "SELECT * FROM 411redsp10.SpeedLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt11);
        preparedStatement.setInt(1,spl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            spl1.setType(resultSet.getInt("type"));
            spl1.setStudentID(resultSet.getInt("student_id"));
            spl1.setLocationID(resultSet.getInt("location_id"));
            spl1.setCurrSpeed(resultSet.getInt("curr_speed"));
            spl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            spl1.setDuration(resultSet.getInt("duration"));
        }
    }

    public void getDistanceLog( DistanceLog disl1 ) throws SQLException {
        // Statements allow to issue SQL queries to the database
        String stmt12 = "SELECT * FROM 411redsp10.DistanceLogs WHERE log_id = ?";
        preparedStatement = connect.prepareStatement(stmt12);
        preparedStatement.setInt(1,disl1.getID());
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            disl1.setType(resultSet.getInt("type"));
            disl1.setStudentID(resultSet.getInt("student_id"));
            disl1.setLocationID(resultSet.getInt("location_id"));
            disl1.setCurrSpeed(resultSet.getInt("curr_speed"));
            disl1.setDistance(resultSet.getDouble("distance"));
            disl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            disl1.setDuration(resultSet.getInt("duration"));
        }
    }

	public void getStopLogsbyStID(ArrayList<StopSignLog> stoplogs, Integer studentid) throws SQLException {
		String stmt = "select * from 411redsp10.StopLogs where student_id=?";
		preparedStatement = connect.prepareStatement(stmt);
		preparedStatement.setInt(1,studentid);
		resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
        	StopSignLog stsl = new StopSignLog();

            stsl.setID(resultSet.getInt("log_id"));
            stsl.setType(resultSet.getInt("type"));
            stsl.setStudentID(resultSet.getInt("student_id"));
            stsl.setLocationID(resultSet.getInt("location_id"));
            stsl.setTimeSt(resultSet.getTimestamp("timestamp"));

            stoplogs.add(stsl);
        }
	}

    public void getDistanceLogsbyStID(ArrayList<DistanceLog> distancelogs, Integer studentid) throws SQLException {
        String stmt = "select * from 411redsp10.DistanceLogs where student_id=?";
        preparedStatement = connect.prepareStatement(stmt);
        preparedStatement.setInt(1,studentid);
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            DistanceLog dstl1 = new DistanceLog();

            dstl1.setID(resultSet.getInt("log_id"));
            dstl1.setType(resultSet.getInt("type"));
            dstl1.setStudentID(resultSet.getInt("student_id"));
            dstl1.setLocationID(resultSet.getInt("location_id"));
            dstl1.setCurrSpeed(resultSet.getInt("curr_speed"));
            dstl1.setDistance(resultSet.getDouble("distance"));
            dstl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            dstl1.setDuration(resultSet.getInt("duration"));

            distancelogs.add(dstl1);
        }
    }

    public void getGeneralLogsbyStID(ArrayList<GeneralLog> genlogs, Integer studentid) throws SQLException {
        String stmt = "select * from 411redsp10.GenLogs where student_id=?";
        preparedStatement = connect.prepareStatement(stmt);
        preparedStatement.setInt(1,studentid);
        resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            GeneralLog genl1 = new GeneralLog();

            genl1.setID(resultSet.getInt("log_id"));
            genl1.setType(resultSet.getInt("type"));
            genl1.setStudentID(resultSet.getInt("student_id"));
            genl1.setLocationID(resultSet.getInt("location_id"));
            genl1.setTimeSt(resultSet.getTimestamp("timestamp"));
            genl1.setDuration(resultSet.getInt("duration"));

            genlogs.add(genl1);
        }
    }

    public void getSpeedLogsbyStID(ArrayList<SpeedLogReport> speedlogs, Integer studentid) 
    	throws SQLException {
            String stmt = "select 411redsp10.SpeedLogs.log_id,411redsp10.SpeedLogs.type," +
            		"411redsp10.SpeedLogs.student_id,411redsp10.SpeedLogs.location_id,411redsp10.SpeedLogs.curr_speed," +
            		"411redsp10.SpeedLogs.timestamp,411redsp10.SpeedLogs.duration,411redsp10.SpeedLimits.maxspeed from " +
            		"411redsp10.SpeedLogs,411redsp10.SpeedLimits where 411redsp10.SpeedLogs.location_id=411redsp10.SpeedLimits.location_id and student_id=?";
            preparedStatement = connect.prepareStatement(stmt);
            preparedStatement.setInt(1,studentid);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                SpeedLogReport splr1 = new SpeedLogReport();

                splr1.setID(resultSet.getInt("log_id"));
                splr1.setType(resultSet.getInt("type"));
                splr1.setStudentID(resultSet.getInt("student_id"));
                splr1.setLocationID(resultSet.getInt("location_id"));
                splr1.setCurrSpeed(resultSet.getInt("curr_speed"));
                splr1.setTimeSt(resultSet.getTimestamp("timestamp"));
                splr1.setDuration(resultSet.getInt("duration"));
                splr1.setSpeedLimit(resultSet.getInt("maxspeed"));

                speedlogs.add(splr1);
            }
    }

    public void getAllStudents(ArrayList<Student> mystudents) throws SQLException  {
 	   
    	if(mystudents.size() > 0) {
    		mystudents.clear();
    	}
    	
       statement=connect.createStatement();
 	   resultSet = statement.executeQuery("select * from 411redsp10.Student");

	   while (resultSet.next()) {
		    Student newstudent = new Student();
		   
		   	newstudent.setID(resultSet.getInt("student_id"));
			newstudent.setName(resultSet.getString("name"));
			newstudent.setInstrID(resultSet.getInt("instr_id"));
			newstudent.setSchoolID(resultSet.getInt("school_id"));
			newstudent.setHours(resultSet.getDouble("hours"));
			newstudent.setAddress(resultSet.getString("address"));
			newstudent.setFingerPrint(resultSet.getBlob("fingerprint"));
	        
	        mystudents.add(newstudent);
	   }
 	   
    }
    
	public void addStudent(Student st2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.Student(name,instr_id,school_id,hours," +
        		"address,fingerprint) values (?, ?, ?, ?, ?, ?)");
        preparedStatement.setString(1, st2.getName());
        preparedStatement.setInt(2, st2.getInstrID());
        preparedStatement.setInt(3, st2.getSchoolID());
        preparedStatement.setDouble(4, st2.getHours());
        preparedStatement.setString(5, st2.getAddress());
        preparedStatement.setBlob(6, st2.getFingerPrint());
        preparedStatement.executeUpdate();
	}

    public void addSchool(School sc2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.School(address,name,owner) values (?, ?, ?)");
        preparedStatement.setString(1, sc2.getAddress());
        preparedStatement.setString(2, sc2.getName());
        preparedStatement.setString(3, sc2.getOwner());
		preparedStatement.executeUpdate();
    }

    public void addInstructor(Instructor instr2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.Instructor(name,school_id) values (?, ?)");
        preparedStatement.setString(1, instr2.getName());
        preparedStatement.setInt(2, instr2.getSchoolID());
        preparedStatement.executeUpdate();
    }

    public void addSpeedLimit(SpeedLimit spl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.SpeedLimits(point1Lat,point1Long," +
        		"point2Lat,point2Long,length,width,maxspeed) values (?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setDouble(1, spl2.getP1Lat());
        preparedStatement.setDouble(2, spl2.getP1Long());
        preparedStatement.setDouble(3, spl2.getP2Lat());
        preparedStatement.setDouble(4, spl2.getP2Long());
        preparedStatement.setDouble(5, spl2.getLength());
        preparedStatement.setDouble(6, spl2.getWidth());
        preparedStatement.setInt(7, spl2.getMaxSpeed());
        preparedStatement.executeUpdate();
    }

    public void addStopSign(StopSign sts2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.StopSigns(point1Lat,point1Long," +
        		"point2Lat,point2Long,length,width,triggerCheck) values (?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setDouble(1, sts2.getP1Lat());
        preparedStatement.setDouble(2, sts2.getP1Long());
        preparedStatement.setDouble(3, sts2.getP2Lat());
        preparedStatement.setDouble(4, sts2.getP2Long());
        preparedStatement.setDouble(5, sts2.getLength());
        preparedStatement.setDouble(6, sts2.getWidth());
        preparedStatement.setBoolean(7, sts2.getTriggerCheck());
        preparedStatement.executeUpdate();
    }

    public void addNewSpeedLimit(NewSpeedLimit newspl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.SpeedLimitsNew(point1Lat," +
        		"point1Long,point2Lat,point2Long,length,width,maxspeed) values (?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setDouble(1, newspl2.getP1Lat());
        preparedStatement.setDouble(2, newspl2.getP1Long());
        preparedStatement.setDouble(3, newspl2.getP2Lat());
        preparedStatement.setDouble(4, newspl2.getP2Long());
        preparedStatement.setDouble(5, newspl2.getLength());
        preparedStatement.setDouble(6, newspl2.getWidth());
        preparedStatement.setInt(7, newspl2.getMaxSpeed());
        preparedStatement.executeUpdate();
    }

    public void addNewStopSign(NewStopSign newsts2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.StopSignsNew(point1Lat," +
        		"point1Long,point2Lat,point2Long,length,width,triggerCheck) values (?, ?, ?, ?, ?, ?, ?)");
        preparedStatement.setDouble(1, newsts2.getP1Lat());
        preparedStatement.setDouble(2, newsts2.getP1Long());
        preparedStatement.setDouble(3, newsts2.getP2Lat());
        preparedStatement.setDouble(4, newsts2.getP2Long());
        preparedStatement.setDouble(5, newsts2.getLength());
        preparedStatement.setDouble(6, newsts2.getWidth());
        preparedStatement.setBoolean(7, newsts2.getTriggerCheck());
        preparedStatement.executeUpdate();
    }


    public void addStopSignLog(StopSignLog stsl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.StopLogs(type,student_id,location_id) " +
        		"values (?, ?, ?)");
        preparedStatement.setInt(1, stsl2.getType());
        preparedStatement.setInt(2, stsl2.getStudentID());
        preparedStatement.setInt(3, stsl2.getLocationID());
        preparedStatement.executeUpdate();
    }

    public void addGeneralLog(GeneralLog genl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.GenLogs(type,student_id,location_id," +
        		"duration) values (?, ?, ?, ?)");
        preparedStatement.setInt(1, genl2.getType());
        preparedStatement.setInt(2, genl2.getStudentID());
		preparedStatement.setInt(3, genl2.getLocationID());
		preparedStatement.setInt(4, genl2.getDuration());
		preparedStatement.executeUpdate();
    }

    public void addSpeedLog(SpeedLog splog2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.SpeedLogs(type,student_id,location_id," +
        		"curr_speed,duration) values (?, ?, ?, ?, ?)");
        preparedStatement.setInt(1, splog2.getType());
        preparedStatement.setInt(2, splog2.getStudentID());
        preparedStatement.setInt(3, splog2.getLocationID());
		preparedStatement.setInt(4, splog2.getCurrSpeed());
        preparedStatement.setInt(5, splog2.getDuration());
        preparedStatement.executeUpdate();
    }

    public void addDistanceLog(DistanceLog dsl2) throws SQLException {
        preparedStatement = connect.prepareStatement("insert into  411redsp10.DistanceLogs(type,student_id," +
        		"location_id,curr_speed,distance,duration) values (?, ?, ?, ?, ?, ?)");
        preparedStatement.setInt(1, dsl2.getType());
        preparedStatement.setInt(2, dsl2.getStudentID());
        preparedStatement.setInt(3, dsl2.getLocationID());
        preparedStatement.setInt(4, dsl2.getCurrSpeed());
		preparedStatement.setDouble(5, dsl2.getDistance());
        preparedStatement.setInt(6, dsl2.getDuration());
        preparedStatement.executeUpdate();
    }

    public void dsm() throws SQLException {

        String pw = "c$411r3d$p10";
        Connection connectnew = null;
        Statement statementnew = null;
        PreparedStatement preparedStatementnew = null;
        connectnew = DriverManager.getConnection("jdbc:mysql://raphael.cs.odu.edu/411redsp10new?" + "user=411redsp10&password="+pw);

        // Get all data from teh StudentNew table on the OBU and drop it into the Client Software
        statementnew = connectnew.createStatement();
        resultSet = statementnew.executeQuery("select * from 411redsp10new.StudentNew");
        while(resultSet.next()) {
            preparedStatement = connect.prepareStatement("insert into  411redsp10.Student(name,instr_id,school_id,hours,address,fingerprint) values (?, ?, ?, ?, ?, ?)");
            preparedStatement.setString(1, resultSet.getString("name"));
            preparedStatement.setInt(2, resultSet.getInt("instr_id"));
            preparedStatement.setInt(3, resultSet.getInt("school_id"));
            preparedStatement.setInt(4, resultSet.getInt("hours"));
            preparedStatement.setString(5, resultSet.getString("address"));
            preparedStatement.setBlob(6,resultSet.getBlob("fingerprint"));
            preparedStatement.executeUpdate();
        }

        // Get all Speed Log Table data from the OBU DB and store it in the Client Software
        statementnew = connectnew.createStatement();
        resultSet = statementnew.executeQuery("select * from 411redsp10new.SpeedLogs");
        while(resultSet.next()) {
            preparedStatement = connect.prepareStatement("insert into 411redsp10.SpeedLogs(type,student_id,location_id,curr_speed,timestamp,duration) values (?, ?, ?, ?, ?, ?)");
            preparedStatement.setInt(1, resultSet.getInt("type"));
            preparedStatement.setInt(2, resultSet.getInt("student_id"));
            preparedStatement.setInt(3, resultSet.getInt("location_id"));
            preparedStatement.setDouble(4, resultSet.getDouble("curr_speed"));
            preparedStatement.setTimestamp(5, resultSet.getTimestamp("timestamp"));
            preparedStatement.setInt(6, resultSet.getInt("duration"));
            preparedStatement.executeUpdate();
        }
        preparedStatement = connectnew.prepareStatement("DELETE from 411redsp10new.SpeedLogs");
        preparedStatement.executeUpdate();



        // Get all Stop Log Table data from the OBU DB and store it in the Client Software
        statementnew = connectnew.createStatement();
        resultSet = statementnew.executeQuery("select * from 411redsp10new.StopLogs");
        while(resultSet.next()) {
            preparedStatement = connect.prepareStatement("insert into 411redsp10.StopLogs" +
            		"(type,student_id,location_id,timestamp) values (?, ?, ?, ?)");
            preparedStatement.setInt(1, resultSet.getInt("type"));
            preparedStatement.setInt(2, resultSet.getInt("student_id"));
            preparedStatement.setInt(3, resultSet.getInt("location_id"));
            preparedStatement.setTimestamp(4, resultSet.getTimestamp("timestamp"));
            preparedStatement.executeUpdate();
        }
        
        preparedStatement = connectnew.prepareStatement("DELETE from 411redsp10new.StopLogs");
        preparedStatement.executeUpdate();


        // Get all General Log Table data from the OBU DB and store it in the Client Software
        statementnew = connectnew.createStatement();
        resultSet = statementnew.executeQuery("select * from 411redsp10new.GenLogs");
        
        while(resultSet.next()) {
            preparedStatement = connect.prepareStatement("insert into 411redsp10.GenLogs(" +
            		"type,student_id,location_id,timestamp,duration) values(?, ?, ?, ?, ?)");
            preparedStatement.setInt(1, resultSet.getInt("type"));
            preparedStatement.setInt(2, resultSet.getInt("student_id"));
            preparedStatement.setInt(3, resultSet.getInt("location_id"));
            preparedStatement.setTimestamp(4, resultSet.getTimestamp("timestamp"));
            preparedStatement.setInt(5, resultSet.getInt("duration"));
            preparedStatement.executeUpdate();
        }
        
        preparedStatement = connectnew.prepareStatement("DELETE from 411redsp10new.GenLogs");
        preparedStatement.executeUpdate();

        // Get all Distance Log Table data from the OBU DB and store it in the Client Software
        statementnew = connectnew.createStatement();
        resultSet = statementnew.executeQuery("select * from 411redsp10new.DistanceLogs");
        
        while(resultSet.next()) {
            preparedStatement = connect.prepareStatement("insert into 411redsp10.DistanceLogs" +
            		"(type,student_id,location_id,curr_speed,distance,timestamp,duration) values (?, ?, ?, ?, ?, ?, ?)");
            preparedStatement.setInt(1, resultSet.getInt("type"));
            preparedStatement.setInt(2, resultSet.getInt("student_id"));
            preparedStatement.setInt(3, resultSet.getInt("location_id"));
            preparedStatement.setDouble(4, resultSet.getDouble("curr_speed"));
            preparedStatement.setDouble(5, resultSet.getDouble("distance"));
            preparedStatement.setTimestamp(6, resultSet.getTimestamp("timestamp"));
            preparedStatement.setInt(7, resultSet.getInt("duration"));
            preparedStatement.executeUpdate();
        }
        
        preparedStatement = connectnew.prepareStatement("DELETE from 411redsp10new.DistanceLogs");
        preparedStatement.executeUpdate();

        // Clear out the Students table in the OBU DB
        preparedStatementnew = connectnew.prepareStatement("DELETE from 411redsp10new.Student");
        preparedStatementnew.executeUpdate();

        // Remove all elements of the StudentNew table on the OBU DB
        preparedStatementnew = connectnew.prepareStatement("DELETE from 411redsp10new.StudentNew");
        preparedStatementnew.executeUpdate();


        // Reset the auto_increment
        statement = connect.createStatement();
        resultSet = statement.executeQuery("select auto_increment,table_name,table_schema from " +
        		"information_schema.tables where table_name = 'Student' and table_schema = '411redsp10'");
        while (resultSet.next()) {
            preparedStatementnew = connect.prepareStatement("ALTER TABLE 411redsp10new.Student auto_increment = ?");
            preparedStatementnew.setInt(1,resultSet.getInt("auto_increment"));
            preparedStatementnew.executeUpdate();
        }

        // Update the Student table on the OBU with whatever is in the Client Software
        statement = connect.createStatement();
        resultSet = statement.executeQuery("select * from 411redsp10.Student");
        while(resultSet.next()) {
            preparedStatementnew = connectnew.prepareStatement("insert into 411redsp10new.Student(" +
            		"student_id,name,instr_id,school_id,hours,address,fingerprint) values (?, ?, ?, ?, ?, ?, ?)");
            preparedStatementnew.setInt(1,resultSet.getInt("student_id"));
            preparedStatementnew.setString(2, resultSet.getString("name"));
            preparedStatementnew.setInt(3, resultSet.getInt("instr_id"));
            preparedStatementnew.setInt(4, resultSet.getInt("school_id"));
            preparedStatementnew.setInt(5, resultSet.getInt("hours"));
            preparedStatementnew.setString(6, resultSet.getString("address"));
            preparedStatementnew.setBlob(7,resultSet.getBlob("fingerprint"));
            preparedStatementnew.executeUpdate();
        }

        // Remove all elements of the StudentNew table on the OBU DB
        preparedStatementnew = connectnew.prepareStatement("DELETE from 411redsp10new.StudentNew");
        preparedStatementnew.executeUpdate();

        if (statementnew != null) {
                statementnew.close();
        }

        if (connectnew != null) {
                connectnew.close();
        }
    }
        
        
        
	// You need to close the resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}
